﻿/*
 * File nay se thuc thi giao dien cua interface IBookTicketRepository
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;
using QLMB.ViewModels;
using QLMB.Infrastructure;

namespace QLMB.Models
{
    public class BookTicketRepository : IBookTicketRepository
    {
        private BookingEntities db = new BookingEntities();
        public BookingEntities DB
        {
            get { return db; }
            set { db = value; }
        }

        public void CommitChanges()
        {
            DB.SaveChanges();
        }

        #region BookTicket
        public IDictionary<string, string> LayDSSanbaydi()
        {
            var DSSB = (from p in db.DUONGBAYs
                       select new
                       {
                           SanBayID = p.SanBayDi,
                           ThanhPho = p.SANBAY.THANHPHO.TenTP
                       }).Distinct();
            IDictionary<string, string> result = new Dictionary<string, string>();
            foreach (var SB in DSSB)
            {
                result.Add(SB.SanBayID,SB.ThanhPho);
            }
            return result;
        }
        public string LayMaDuongBay(string NoiDi, string NoiDen)
        {
            return (from p in db.DUONGBAYs
                    where (p.SanBayDi == NoiDi && p.SanBayDen == NoiDen)
                    select p).First().MaDuongBay;
        }
        public string LayTenTP(string MaSB)
        {
            return (from p in db.SANBAYs
                    where (p.MaSB == MaSB)
                    select p).First().THANHPHO.TenTP;
        }
        public IEnumerable<CHUYENBAY> LayDSChuyenBay(DateTime Ngaydi, string Sanbaydi, string Sanbayden)
        {   
            string mdb = LayMaDuongBay(Sanbaydi,Sanbayden);
            Ngaydi = Ngaydi.Date;
            DateTime nextday = Ngaydi.AddDays(1);
            IEnumerable<CHUYENBAY> DSCB = from p in db.CHUYENBAYs
                                          where (p.NgayDi.Value >= Ngaydi && p.NgayDi.Value < nextday && p.MaDuongBay == mdb)
                                          select p;
            return DSCB;
        }
        #endregion

        #region Router Map
        // Lấy danh sách các thành phố có thể tạo chuyến bay
        public List<SelectListItem> LayDSTP()
        {
            var DSTP = (from p in db.DUONGBAYs
                       where ((p.HangKhaiThac.CompareTo("AIRLINE"))==0)
                       orderby p.SANBAY.THANHPHO.QuocGia
                       select new SelectListItem
                       {
                           Value = p.SANBAY.MaSB,
                           Text = p.SANBAY.THANHPHO.TenTP
                       }).Distinct();
            return DSTP.ToList();
        }
        public List<SelectListItem> LayDSTPDen(string id)
        {
            var DSTPDen = (from p in db.DUONGBAYs
                           where (p.SANBAY.MaSB == id && p.HangKhaiThac == "AIRLINE")
                           orderby p.SANBAY1.THANHPHO.QuocGia
                           select new SelectListItem
                           {
                               Value = p.SANBAY1.MaSB,
                               Text = p.SANBAY1.THANHPHO.TenTP
                           }).Distinct();
            return DSTPDen.ToList();
        }
        public List<SelectListItem> LayDSTPDi(string id)
        {
            var DSTPDi = (from p in db.DUONGBAYs
                           where (p.SANBAY1.MaSB == id)
                           select new SelectListItem
                           {
                               Value = p.SANBAY.MaSB,
                               Text = p.SANBAY.THANHPHO.TenTP
                           }).Distinct();
            return DSTPDi.ToList();
        }
        // Dựa vào sân bay đến và sân bay đi tìm kiếm loại hành trình của chuyến bay đó
        public List<SelectListItem> LayHanhTrinh(string sbdi, string sbden)
        {
            var hanhtrinh = from p in db.DUONGBAYs
                            where (p.SANBAY.MaSB == sbdi && p.SANBAY1.MaSB == sbden)
                            select new SelectListItem
                            {
                                Text = p.HanhTrinh,
                                Value = p.MaDuongBay
                            };
            return hanhtrinh.ToList();
        }
        // Lấy mã đường bay của 1 chuyến bay thẳng
        public List<SelectListItem> LayMaDB_BayThang(string sbdi, string sbden)
        {
            var MaDb = from p in db.DUONGBAYs
                       where (p.SANBAY.MaSB == sbdi && p.SANBAY1.MaSB == sbden)
                       select new SelectListItem
                       {
                           Value = p.MaDuongBay,
                           Text = p.LoaiDuongBay
                       };
            return MaDb.ToList();
        }
        // Lấy danh sách đường bay nối tiếp của một chuyến bay hợp tác
        public List<SelectListItem> LayMaDB_HopTac(string sbdi, string sbden)
        {
                var MaDb = from p in db.CT_DUONGBAY
                           where (p.DUONGBAY.SANBAY.MaSB == sbdi && p.DUONGBAY.SANBAY1.MaSB == sbden)
                           select new SelectListItem
                           {
                               Value = p.MaDuongBayNoiTiep,
                               Text = p.DUONGBAY.HangKhaiThac
                           };
                return MaDb.ToList();
        }
        public int? LayThoiGianBay(string madb)
        {
            var thoigianbay = (from p in db.DUONGBAYs
                               where (p.MaDuongBay == madb)
                               select p.ThoiGianBay).Single();
            return thoigianbay;
        }
        public List<SelectListItem> LayDSMayBay(string loaidb, DateTime ngaydi, DateTime ngayden)
        {
            var checkcb = (from p in db.CHUYENBAYs
                           where !(ngayden < p.NgayDi || ngaydi> p.NgayDen)
                           select p.MaMB).Distinct();
            List<string> li_checkdb = checkcb.ToList();
            var DSMB = from p in db.MAYBAYs
                       where p.LoaiDuongBay == loaidb 
                       && !li_checkdb.Contains(p.MaMB)
                       select new SelectListItem
                       {
                           Value = p.MaMB,
                           Text = p.TenMB
                       };
            return DSMB.ToList();
        }
        // Lấy sân bay đi và sân bay đến dựa vào mã đường bay
        public List<FlightItem> LaySanBay(string madb)
        {
            var sb = (from p in db.CT_DUONGBAY
                     where p.MaDuongBayChinh == madb
                     orderby p.STT
                     select new FlightItem
                     {
                         MaDB = p.MaDuongBayNoiTiep,
                         SbDen = p.DUONGBAY1.SanBayDen,
                         SbDi = p.DUONGBAY1.SanBayDi,
                         HangKhaiThac = p.DUONGBAY1.HangKhaiThac,
                         ThoiGianBay = p.DUONGBAY1.ThoiGianBay,
                         STT = p.STT
                     });
            return sb.ToList();
        }
        // Tạo chuyến bay noi dia
        public bool CreateFlight(FlightItem flight, string mamb, int loaicb)
        {
            int num;
            CHUYENBAY cb = new CHUYENBAY();
            cb.MaDuongBay = flight.MaDB;
            cb.TongSoGheDaDat = 0;
            cb.NgayDi = flight.NgayDi;
            cb.NgayDen = flight.NgayDen;
            cb.TrangThaiCB = "San sang";
            var temp = from p in db.CHUYENBAYs
                       where p.MaCB.Contains("VN")
                       select p.MaCB;
            List<string> li = temp.ToList();
            if (temp.Count() == 0)
            {
                num = 1;
            }
            else
            {
                num = int.Parse(li.Last().Substring(2)) + 1;
            }
            cb.MaCB = "VN" + num.ToString();

            if (flight.Maybay == 1)
            {
                cb.MaMB = mamb;
            }
            // lấy lên thông tin về ghế của máy bay và insert vào bảng CT_Ghế chuyến bay
            var ghemaybays = from p in db.GHEMAYBAYs
                            where p.MaMB == cb.MaMB
                            select p.MaGhe;
            List<string> li_ghemaybay = ghemaybays.ToList();

            try
            {
                db.AddToCHUYENBAYs(cb);
                db.SaveChanges();
                foreach (var ghe in li_ghemaybay)
                {
                    CT_GHECHUYENBAY ghecb = new CT_GHECHUYENBAY { MaGhe = ghe, MaCB = cb.MaCB, SoLuongGheDaDat = 0 };
                    db.AddToCT_GHECHUYENBAY(ghecb);
                    db.SaveChanges();
                }
            }
            catch (System.Exception ex)
            {
                return false;

            }
            return true;
        }
        // tao chuyen bay nhieu chang, tao chuyen bay tu A->D
        public string CreateFlight(DateTime ngaydi, string madb)
        {
            int num;
            CHUYENBAY cb = new CHUYENBAY();
            cb.MaDuongBay = madb;
            cb.NgayDi = ngaydi;
            cb.TrangThaiCB = "Sẵn sàng";
            var temp = from p in db.CHUYENBAYs
                       where p.MaCB.Contains("QT")
                       select p.MaCB;
            List<string> li = temp.ToList();
            if (temp.Count() == 0)
            {
                num = 1;
            }
            else
            {
                num = int.Parse(li.Last().Substring(2)) + 1;
            }
            cb.MaCB = "QT" + num.ToString();

            try
            {
                db.AddToCHUYENBAYs(cb);
                db.SaveChanges();
            }
            catch (System.Exception ex)
            {
                return "false";
            }
            return cb.MaCB;
        }
        // Tao cac chuyen bay trung gian cua chuyen bay nhieu chang co ma la macb
        public bool CreateFlight(FlightItem flight, string mamb, int loaicb, string macb, int stt)
        {
            if (macb == "false") return false;
            CHUYENBAY cb = new CHUYENBAY();
            cb.MaDuongBay = flight.MaDB;
            cb.TongSoGheDaDat = 0;
            cb.NgayDi = flight.NgayDi;
            cb.NgayDen = flight.NgayDen;
            cb.TrangThaiCB = "San sang";
            if (flight.Maybay == 1)
            {
                cb.MaMB = mamb;
            }
            cb.MaCB = macb+ "_" + stt.ToString();
            try
            {
                db.AddToCHUYENBAYs(cb);
                db.SaveChanges();
            }
            catch (System.Exception ex)
            {
                return false;
            }
            return true;
        }
        public List<CHUYENBAY> ListAllFlight()
        {
            DateTime now = DateTime.Now.Date;
            var allflight = from p in db.CHUYENBAYs
                            where p.NgayDi >= now && !p.MaCB.Contains("_")
                            select p;
            return allflight.ToList();
        }
        // Lay thong tin day du cua 1 chuyen bay 
        public FlightItem LayThongTinChuyenBay(CHUYENBAY cb)
        {
            var thongtincb = from p in db.CHUYENBAYs
                             where p.MaCB == cb.MaCB
                             select new FlightItem
                             {
                                 TrangThaiCB = p.TrangThaiCB,
                                 MaDB = p.MaDuongBay,
                                 MaCB = p.MaCB,
                                 SbDi = p.DUONGBAY.SanBayDi,
                                 SbDen = p.DUONGBAY.SanBayDen,
                                 HangKhaiThac = p.DUONGBAY.HangKhaiThac,
                                 TongSoGheDaDat = p.TongSoGheDaDat,
                                 ThoiGianBay = p.DUONGBAY.ThoiGianBay,
                                 STT = 0,
                                 Maybay = 0,
                                 NgayDi = (DateTime)p.NgayDi,
                                 NgayDen = (DateTime)p.NgayDen,
                                 MaMB = p.MaMB
                             };
            return thongtincb.First();
        }
        #endregion
    }
}